﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Classes;

namespace FlightSystemDBLayer
{
    public class DBFlight : IFlight
    {

        private static SqlCommand cmd = null;

        public DBFlight()
        {

        }
        public  List<Flight> GetFlights()
        {
            List<Flight> list = new List<Flight>();
            string sql = "select * from Flight";
            cmd = SQLAccess.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = cmd.ExecuteReader();

            Flight a;
            City cityFrom;
            City cityTo;
            int idCityFrom;
            int idCityTo;

            while (dbReader.Read())
            {
                idCityFrom = (Convert.ToInt32(dbReader["IDDepartureCity"]));
                idCityTo = (Convert.ToInt32(dbReader["IDArrivalCity"]));
                DBCity dbCity = new DBCity();
                cityFrom = dbCity.FindCityByID(idCityFrom);
                SQLAccess.Close();
                cityTo = dbCity.FindCityByID(idCityTo);
                dbReader.Read();
                a = new Flight(cityFrom, cityTo);

                list.Add(a);
             //   dbReader.Close();

            }
            SQLAccess.Close();
            return list;

        }
        public  void CreateFlight(int cityFromID, int cityToID, string company, int noOfSeats)
        {
            string sql = @"insert into Flight values (" + cityFromID + ", " + cityToID + ", " + company + ", " + noOfSeats + ",)";
            SqlCommand cmd = SQLAccess.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            SQLAccess.Close();
        }
        public Flight FindFlightByID(int idFlight)
        {
            string sql = @"select * from Flight where IDFlight = " + idFlight;
            cmd = SQLAccess.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = cmd.ExecuteReader();

            Flight a;
            City cityFrom;
            City cityTo;
            int idCityFrom;
            int idCityTo;

            if (dbReader.Read())
            {
                idCityFrom = (Convert.ToInt32(dbReader["IDDepartureCity"]));
                idCityTo = (Convert.ToInt32(dbReader["IDArrivalCity"]));
                DBCity dbCity = new DBCity();
                cityFrom = dbCity.FindCityByID(idCityFrom);
                cityTo = dbCity.FindCityByID(idCityTo);
                a = new Flight(cityFrom, cityTo);
            }
            else
            {
                a = null;
                dbReader.Close();
                SQLAccess.Close();

            }
            return a;
        }
    
    
    
    }

}
